package com.dhcc.DRGsHosp.application.impl;

import com.dhcc.DRGsHosp.application.DrgsServiceApplication;
import com.dhcc.DRGsHosp.core.domain.MrTransDep;
import com.dhcc.DRGsHosp.facade.DrgsIndexCalFacade;
import org.dayatang.domain.EntityRepository;
import org.dayatang.domain.InstanceFactory;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.dayatang.querychannel.QueryChannelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.inject.Named;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @title		:DRGsServiceApplicationImpl.java
 * @description	:
 * @author		:WangTeng
 * @date		:2017年9月5日
 * @version     :1.0
 * @copyright   :DHCC 2013-2014 
 */
@Named
@Transactional
public class DrgsServiceApplicationImpl implements DrgsServiceApplication {
	
	
	@Autowired
	private NamedParameterJdbcTemplate jdbcTempSeverice;
	
	@Autowired
	private DrgsIndexCalFacade drgsIndexCalFacade;
	
    @Autowired  
    private JdbcTemplateObj jdbcTemplateObj; 
    
    private EntityRepository repository;
    private QueryChannelService queryChannel;
	
	private EntityRepository getRepository() {
		if (repository == null) {
			repository = InstanceFactory.getInstance(
					EntityRepository.class, "repository");
		}
		return repository;
	}
	 private QueryChannelService getQueryChannelService(){
	       if(queryChannel==null){
	          queryChannel = InstanceFactory.getInstance(QueryChannelService.class,"queryChannel");
	       }
	     return queryChannel;
	    }
	@Override
	public List<Map<String, Object>> findDeathDetail(String yearMonth) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("P00060000,");
		sql.append("P00070000,");
		sql.append("P00090000,");
		sql.append("P00050000,");
		sql.append("P00600300,");
		sql.append("P00020000,");
		sql.append("P00940001, ");
		sql.append("P00220400 ");
		sql.append("FROM ");
		sql.append("DRGS.MR_BASEINFO B ");
		sql.append("INNER JOIN DRGS.MR_IPINFO I ON B.PMAININDEX = I.PMAININDEX ");
		sql.append("WHERE 1=1 ");
		sql.append("AND I.P00620001 = '5' ");//死亡病例
		sql.append("AND TO_CHAR (I.P00290000, 'YYYY-MM') =:yearMonth ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("yearMonth", yearMonth)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("name", obj[0]);
			map.put("sex", obj[1]);
			map.put("age", obj[2]);
			map.put("medicalRecords", obj[3]);
			map.put("careDoctor", obj[4]);
			map.put("expenseCategory", obj[5]);
			map.put("diagnose", obj[6]);
			map.put("adm", obj[7]);
			map.put("bedNum", null);
			map.put("deathTime", null);
			map.put("deathNodule", null);
			
			resultList.add(map);
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findLowRiskDeathNum(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findLowRiskDeathNum(String year) {
		String asId = drgsIndexCalFacade.getStdID(year,year);
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("TO_CHAR (P00290000, 'YYYY-MM'),");
		sql.append("SUM (CASE WHEN ipinfo.p00620001 = '5' AND c.as_risk_level = 1 THEN 1 ELSE 0 END) ");
		sql.append("FROM ");
		sql.append("DRGS.MR_BASEINFO B ");
		sql.append("LEFT JOIN drgs.mr_ipinfo ipinfo ON B .pmainindex = ipinfo.pmainindex ");
		sql.append("LEFT JOIN DRGs.t_patientdrg P ON B .pmainindex = P.parid ");
		sql.append("LEFT JOIN DRGs.t_annualstd c ON P.pardrg_code = c.as_drg_code ");
		sql.append("AND c.AS_ID =:asId ");
		sql.append("WHERE 1=1 ");
		sql.append("AND TO_CHAR (P00290000, 'YYYY') =:year ");
		sql.append("GROUP BY TO_CHAR (P00290000, 'YYYY-MM') ");
		sql.append("ORDER BY TO_CHAR (P00290000, 'YYYY-MM') ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("year", year)
				.addParameter("asId", asId)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("month", obj[0]);
			map.put("lowRiskDeathNum", obj[1]);
			
			resultList.add(map);
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findLowRiskDeathDetail(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findLowRiskDeathDetail(String yearMonth) {
		String asId = drgsIndexCalFacade.getStdID(yearMonth,yearMonth);
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("P00060000,");
		sql.append("P00070000,");
		sql.append("P00090000,");
		sql.append("P00050000,");
		sql.append("P00600300,");
		sql.append("P00020000,");
		sql.append("P00940001, ");
		sql.append("P00220400 ");
		sql.append("FROM ");
		sql.append("DRGS.MR_BASEINFO B ");
		sql.append("INNER JOIN DRGS.MR_IPINFO I ON B.PMAININDEX = I.PMAININDEX ");
		sql.append("LEFT JOIN DRGs.t_patientdrg P ON B.pmainindex = P.parid ");
		sql.append("LEFT JOIN DRGs.t_annualstd c ON p.pardrg_code = c.as_drg_code ");
		sql.append("AND c.AS_ID =:asId ");
		sql.append("WHERE 1=1 ");
		sql.append("AND I.P00620001 = '5' ");//死亡病例
		sql.append("AND c.as_risk_level = '1' ");
		sql.append("AND TO_CHAR (I.P00290000, 'YYYY-MM') =:yearMonth ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("yearMonth", yearMonth)
				.addParameter("asId", asId)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("name", obj[0]);
			map.put("sex", obj[1]);
			map.put("age", obj[2]);
			map.put("medicalRecords", obj[3]);
			map.put("careDoctor", obj[4]);
			map.put("expenseCategory", obj[5]);
			map.put("diagnose", obj[6]);
			map.put("adm", obj[7]);
			map.put("bedNum", null);
			map.put("deathTime", null);
			map.put("deathNodule", null);
			
			resultList.add(map);
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findNeonatalDeathNum(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findNeonatalDeathNum(String year) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("TO_CHAR (P00290000, 'YYYY-MM'),");
		sql.append("SUM(CASE WHEN P00110000<1000 THEN 1 ELSE 0 END), ");
		sql.append("SUM(CASE WHEN P00110000>=1000 AND P00110000<3000 THEN 1 ELSE 0 END),");
		sql.append("SUM(CASE WHEN P00110000>=3000 AND P00110000<5000 THEN 1 ELSE 0 END),");
		sql.append("SUM(CASE WHEN P00110000>=5000 AND P00110000<7000 THEN 1 ELSE 0 END),");
		sql.append("SUM(CASE WHEN P00110000>=7000 THEN 1 ELSE 0 END) ");
		sql.append("FROM ");
		sql.append("DRGS.MR_BASEINFO B ");
		sql.append("LEFT JOIN drgs.mr_ipinfo ipinfo ON B .pmainindex = ipinfo.pmainindex ");
		sql.append("WHERE 1=1 ");
		sql.append("AND P00620001 = '5' ");//死亡病例
		sql.append("AND P00110000 IS NOT NULL ");//新生体重不为空
		sql.append("AND TO_CHAR (P00290000, 'YYYY') =:year ");
		sql.append("GROUP BY TO_CHAR (P00290000, 'YYYY-MM') ");
		sql.append("ORDER BY TO_CHAR (P00290000, 'YYYY-MM') ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("year", year)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("month", obj[0]);
			map.put("DeathNum1", obj[1]);
			map.put("DeathNum2", obj[2]);
			map.put("DeathNum3", obj[3]);
			map.put("DeathNum4", obj[4]);
			map.put("DeathNum5", obj[5]);
			
			resultList.add(map);
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findNeonatalDeathDetail(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findNeonatalDeathDetail(String yearMonth) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("P00060000,");
		sql.append("P00070000,");
		sql.append("P00090000,");
		sql.append("P00050000,");
		sql.append("P00600300,");
		sql.append("P00020000,");
		sql.append("P00940001,");
		sql.append("P00220400 ");
		sql.append("FROM ");
		sql.append("DRGS.MR_BASEINFO B ");
		sql.append("INNER JOIN DRGS.MR_IPINFO I ON B.PMAININDEX = I.PMAININDEX ");
		sql.append("WHERE 1=1 ");
		sql.append("AND I.P00620001 = '5' ");//死亡病例
		sql.append("AND P00110000 IS NOT NULL ");//新生儿体重不等于空
		sql.append("AND TO_CHAR (I.P00290000, 'YYYY-MM') =:yearMonth ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("yearMonth", yearMonth)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("name", obj[0]);
			map.put("sex", obj[1]);
			map.put("age", obj[2]);
			map.put("medicalRecords", obj[3]);
			map.put("careDoctor", obj[4]);
			map.put("expenseCategory", obj[5]);
			map.put("diagnose", obj[6]);
			map.put("adm", obj[7]);
			map.put("bedNum", null);
			map.put("deathTime", null);
			map.put("deathNodule", null);
			
			resultList.add(map);
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findSurgicalCompNum(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findSurgicalCompNum(String year) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("TO_CHAR (P00290000, 'YYYY-MM'),");
		sql.append("COUNT(IPINFO.PMAININDEX) ");
		sql.append("FROM ");
		sql.append("DRGS.MR_BASEINFO B ");
		sql.append("LEFT JOIN drgs.mr_ipinfo ipinfo ON B .pmainindex = ipinfo.pmainindex ");
		sql.append("LEFT JOIN DRGs.t_patientdrg P ON B .pmainindex = P.parid ");
		sql.append("WHERE 1=1 ");
		sql.append("AND (");
		sql.append("P.PARDRG_CODE LIKE '___1' ");
		sql.append("OR P.PARDRG_CODE LIKE '___2' ");
		sql.append("OR P.PARDRG_CODE LIKE '___3' ");
		sql.append("OR P.PARDRG_CODE LIKE '___4' ");
		sql.append(") ");
		sql.append("AND (");
		sql.append("P.PARDRG_CODE LIKE '_A__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_B__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_C__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_D__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_E__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_F__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_G__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_H__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_J__' ");
		sql.append(") ");
		sql.append("AND TO_CHAR (P00290000, 'YYYY') =:year ");
		sql.append("GROUP BY TO_CHAR (P00290000, 'YYYY-MM') ");
		sql.append("ORDER BY TO_CHAR (P00290000, 'YYYY-MM') ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("year", year)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("month", obj[0]);
			map.put("surgicalCompNum", obj[1]);
			
			resultList.add(map);
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findSurgicalCompDetail(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findSurgicalCompDetail(String yearMonth) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("CASE WHEN P.PARDRG_CODE LIKE '%1' THEN '重要合并症和伴随病' ");
		sql.append("WHEN P.PARDRG_CODE LIKE '%2' THEN '小于17岁，伴重要合并症和伴随病' ");
		sql.append("WHEN P.PARDRG_CODE LIKE '%3' THEN '一般合并症和伴随病' ");
		sql.append("WHEN P.PARDRG_CODE LIKE '%4' THEN '小于17岁，伴一般合并症和伴随病' ");
		sql.append("END, ");
		sql.append("COUNT(IPINFO.PMAININDEX) ");
		sql.append("FROM ");
		sql.append("DRGS.MR_BASEINFO B ");
		sql.append("LEFT JOIN drgs.mr_ipinfo ipinfo ON B .pmainindex = ipinfo.pmainindex ");
		sql.append("LEFT JOIN DRGs.t_patientdrg P ON B .pmainindex = P.parid ");
		sql.append("WHERE 1=1 ");
		sql.append("AND (");
		sql.append("P.PARDRG_CODE LIKE '___1' ");
		sql.append("OR P.PARDRG_CODE LIKE '___2' ");
		sql.append("OR P.PARDRG_CODE LIKE '___3' ");
		sql.append("OR P.PARDRG_CODE LIKE '___4' ");
		sql.append(") ");
		sql.append("AND (");
		sql.append("P.PARDRG_CODE LIKE '_A__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_B__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_C__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_D__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_E__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_F__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_G__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_H__' ");
		sql.append("OR P.PARDRG_CODE LIKE '_J__' ");
		sql.append(") ");
		sql.append("AND TO_CHAR (P00290000, 'YYYY-MM') =:yearMonth ");
		sql.append("GROUP BY ");
		sql.append("CASE WHEN P.PARDRG_CODE LIKE '%1' THEN '重要合并症和伴随病' ");
		sql.append("WHEN P.PARDRG_CODE LIKE '%2' THEN '小于17岁，伴重要合并症和伴随病' ");
		sql.append("WHEN P.PARDRG_CODE LIKE '%3' THEN '一般合并症和伴随病' ");
		sql.append("WHEN P.PARDRG_CODE LIKE '%4' THEN '小于17岁，伴一般合并症和伴随病' ");
		sql.append("END ");
		sql.append("ORDER BY COUNT (IPINFO.PMAININDEX) DESC ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("yearMonth", yearMonth)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			if (obj[0] != null) {
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("surgicalCompType", obj[0]);
				map.put("number", obj[1]);
				
				resultList.add(map);
			}
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findAdverseEventNum(java.lang.String)
	 */
	@Override
	public List<Map<String, Object>> findAdverseEventNum(String year) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT ");
		sql.append("TO_CHAR (P00290000, 'YYYY-MM'),");
		sql.append("SUM(CASE WHEN P90041001=1 THEN 1 ELSE 0 END), ");
		sql.append("SUM(CASE WHEN P90061001=1 THEN 1 ELSE 0 END) ");
		sql.append("FROM ");
		sql.append("DRGS.MR_IPINFO I ");
		sql.append("LEFT JOIN DRGS.MR_APPENDPAGE A ON I.PMAININDEX = A .PMAININDEX ");
		sql.append("WHERE 1=1 ");
		sql.append("AND TO_CHAR (P00290000, 'YYYY') =:year ");
		sql.append("GROUP BY TO_CHAR (P00290000, 'YYYY-MM') ");
		sql.append("ORDER BY TO_CHAR (P00290000, 'YYYY-MM') ");
		List<Object[]> list = getRepository().createSqlQuery(sql.toString())
				.addParameter("year", year)
				.list();
		List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
		for (Object[] obj : list) {
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("month", obj[0]);
			map.put("adverseEventNum1", null);//输血不良反应人数【暂无】
			map.put("adverseEventNum2", obj[2]);//药品不良反应人数
			map.put("adverseEventNum3", obj[1]);//医疗事件人数
			map.put("adverseEventNum4", null);//跌倒风险人数【暂无】
			map.put("adverseEventNum5", null);//其它人数【暂无】
			
			resultList.add(map);
		}
		return resultList;
	}
	/**
	 * 
	 *  executeQueryForList.  
	 *  @Description：
	 *  @Author ChenHanlin
	 *  @param sql
	 *  @param condition
	 *  @return 
	 *  @Date:2017年9月19日下午3:41:33
	 */
	@Override
	public List<Map<String, Object>> executeQueryForList(String sql,Map<String,Object> condition) {
		List<Map<String, Object>> mapLsit = jdbcTempSeverice.queryForList(sql, condition)  ;
		return mapLsit;
	}
	/**
	 * 
	 *  @Description：根据出院日期+病案号or入院日期+病案号查询主键
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findPmainindex()
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'findPmainindex' + #mrNo + #inDate +#outDate")
	public Map<String,String> findPmainindex(String mrNo,String inDate,String outDate){
		Map<String,String> map=new HashMap<String,String>();
		try{
			StringBuilder sql=new StringBuilder();
			sql.append("SELECT IP.PMAININDEX FROM DRGS.MR_IPINFO IP LEFT JOIN DRGS.MR_BASEINFO BS ON IP.PMAININDEX=BS.PMAININDEX  \n");
			sql.append("WHERE TO_CHAR(IP.P00250000,'YYYY-MM-DD')='"+inDate+"' AND BS.P00050000='"+mrNo+"' AND TO_CHAR(IP.P00290000,'YYYY-MM-DD')='"+outDate+"' \n");
			List<Map<String,Object>> list = jdbcTemplateObj.queryForList(sql.toString());			
			if(list.isEmpty()){
				map.put("mr_id", mrNo+"-"+outDate);
				map.put("flag", "1");
			}else{
				map.put("mr_id",list.get(0).get("PMAININDEX")+"");
				map.put("flag", "0");
			}
		}catch(Exception e){
			e.printStackTrace();
		}	
		return map; 	
	}
	/**
	 * 
	 *  @Description：根据科室代码获取描述
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#getDepCodeByDesc(java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getDepCodeByDesc' + #pt0011")
	public String getDepCodeByDesc(String pt0011) {
		return jdbcTemplateObj.queryForObject("SELECT dep.dep_code FROM drgs.t_department dep WHERE dep.dep_name='"+pt0011+"'", String.class);
	}
	/**
	 * 
	 *  @Description：根据病区代码获取描述
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#getWarCodeByDesc(java.lang.String)
	 */
	@Override
	@Cacheable(value = "drgsIndex3.1.1", key = "'getWarCodeByDesc' + #pt0021")
	public String getWarCodeByDesc(String pt0021) {
		return jdbcTemplateObj.queryForObject("SELECT wa.ward_code FROM drgs.t_ward wa WHERE wa.ward_desc='"+pt0021+"'", String.class);
	}
	/**
	 * 
	 *  @Description：保存转科信息
	 *  @Author LiuHongJun  
	 *  @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#saveMrTransDep(com.dhcc.DRGsHosp.core.domain.MrTransDep)
	 */
	@Override
	public void saveMrTransDep(MrTransDep mrTransDep) {
		mrTransDep.save();	
	}
	/**
	 * 
	 *  @Description：用医保描述从字典中获取相应的code
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#findMedinsType(java.lang.String)
	 */
	@Override
	public Map<String,String> findMedinsType(String medinsType){
		  Map<String,String> map= new HashMap<>();
		  String code=jdbcTemplateObj.queryForObject("SELECT T.DIC_VALUE FROM DRGS.MR_DICTIONARYDATA T WHERE T.DIC_CODE='MED_INS_TYPE' AND T.DIC_DESC='"+medinsType+"'",String.class);
		   if(!StringUtils.hasLength(code)) {
			   insertMedinsType(medinsType);
			   map.put("code", jdbcTemplateObj.queryForObject("SELECT drgs.fn_getpy('"+medinsType+"') from dual", String.class));
		   }else {
			   map.put("code", code);
		   }
		  return map ;	
	}

	/**
	 * 
	 *  @return 
	 * @Description：插入新的医保类型code
	 *  @Author ZouYiyu  
	 *  @see com.dhcc.DRGsHosp.application.DrgsServiceApplication#insertMedinsType(java.lang.String)
	 */
	public void insertMedinsType(String medinsType){
		try{
			String sqlNew="INSERT INTO DRGS.MR_DICTIONARYDATA (DIC_CODE,DIC_VALUE,DIC_DESC)VALUES('MED_INS_TYPE',drgs.fn_getpy('"+medinsType+"'),'"+medinsType+"')";
			getRepository().createSqlQuery(sqlNew).executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	@Override
	public List<Map<String, Object>> findDeptDRGRatio(String year, String deptCode) {
		List<Map<String, Object>> verList = jdbcTemplateObj.queryForList("SELECT D.VER_CODE FROM DRGS.T_DRGVERSION D ORDER BY D.ADD_DATE DESC");
		String version = "";
		if (verList.size()>0 && verList.get(0)!=null){
			version = verList.get(0).get("VER_CODE")+"";
		}
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append("TO_CHAR (I.P00290000, 'YYYY-MM'), ");
        sql.append("ROUND(COUNT (DISTINCT P .PARDRG_CODE)*100/COUNT(DISTINCT D.PARDRG_CODE),2) ");
        sql.append("FROM ");
        sql.append("DRGS.MR_IPINFO I ");
        sql.append("LEFT JOIN DRGS.T_PATIENTDRG P ON I.PMAININDEX = P .PARID ");
        sql.append("LEFT JOIN DRGS.T_DEPARTMENTDRG D ON I.P00300001=D.PARDEP_CODE ");
        sql.append("WHERE 1=1 ");
        sql.append("AND D.VERSION =:drgVersion ");
        sql.append("AND TO_CHAR (P00290000, 'YYYY') =:year ");
        sql.append("AND I.P00300001 =:deptCode ");
        sql.append("GROUP BY TO_CHAR (P00290000, 'YYYY-MM') ");
        sql.append("ORDER BY TO_CHAR (P00290000, 'YYYY-MM') ");
        List<Object[]> list = getRepository().createSqlQuery(sql.toString())
                .addParameter("year", year)
                .addParameter("deptCode", deptCode)
                .addParameter("drgVersion", version)
                .list();
        List<Map<String, Object>> resultList = new ArrayList<Map<String,Object>>();
        for (Object[] obj : list) {
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("yearMonth", obj[0]);
            map.put("diseaseCoverageRate", obj[1]);//病组覆盖率

            resultList.add(map);
        }
        return resultList;
	}
}
